package cn.easyplatform.studio.utils;

import cn.easyplatform.entities.beans.table.TableBean;
import cn.easyplatform.entities.beans.table.TableField;
import cn.easyplatform.entities.beans.table.TableFk;
import cn.easyplatform.entities.beans.table.TableIndex;
import cn.easyplatform.lang.Strings;
import cn.easyplatform.studio.StudioApp;
import cn.easyplatform.studio.cmd.entity.GetEntityCmd;
import cn.easyplatform.type.FieldType;
import org.zkoss.poi.POIXMLDocument;
import org.zkoss.poi.hssf.usermodel.HSSFCell;
import org.zkoss.poi.hssf.usermodel.HSSFWorkbook;
import org.zkoss.poi.poifs.filesystem.POIFSFileSystem;
import org.zkoss.poi.ss.usermodel.Cell;
import org.zkoss.poi.ss.usermodel.Row;
import org.zkoss.poi.ss.usermodel.Sheet;
import org.zkoss.poi.ss.usermodel.Workbook;
import org.zkoss.poi.xssf.usermodel.XSSFWorkbook;
import org.zkoss.util.resource.Labels;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ImportExecl {
    public final static String TABLE = "table";
    public final static String DATA = "data";
    /**
     * 总行数
     */

    private int totalRows = 0;

    /**
     * 总列数
     */

    private int totalCells = 0;

    /**
     * 错误信息
     */

    private String errorInfo;

    /**
     * 构造方法
     */

    public ImportExecl() {

    }

    public int getTotalRows() {

        return totalRows;

    }

    public int getTotalCells() {

        return totalCells;

    }

    public String getErrorInfo() {

        return errorInfo;

    }

    public boolean validateExcel(String filePath) {

        /** 检查文件名是否为空或者是否是Excel格式的文件 */

        if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))) {

            errorInfo = "文件名不是excel格式";

            return false;

        }

        /** 检查文件是否存在 */

        File file = new File(filePath);

        if (file == null || !file.exists()) {

            errorInfo = "文件不存在";

            return false;

        }

        return true;

    }


    public Map<String, Object> read(String filePath, String type) {

        Map<String, Object> execlData = new HashMap<>();

        InputStream is = null;

        try {

            /** 验证文件是否合法 */

            if (!validateExcel(filePath)) {

                System.out.println(errorInfo);

                return null;

            }

            /** 判断文件的类型，是2003还是2007 */

            boolean isExcel2003 = true;


            /** 调用本类提供的根据流读取的方法 */

            File file = new File(filePath);
            is = new FileInputStream(file);
            if (!is.markSupported()) {
                is = new PushbackInputStream(is, 8);
            }
            if (POIFSFileSystem.hasPOIFSHeader(is)) {
                System.out.println("2003及以下");
                isExcel2003 = true;
            }
            if (POIXMLDocument.hasOOXMLHeader(is)) {
                System.out.println("2007及以上");
                isExcel2003 = false;
            }

            execlData = read(is, isExcel2003, type);

            is.close();

        } catch (Exception ex) {

            ex.printStackTrace();

        } finally {

            if (is != null) {

                try {

                    is.close();

                } catch (IOException e) {

                    is = null;

                    e.printStackTrace();

                }

            }

        }

        /** 返回最后读取的结果 */

        return execlData;

    }

    public Map<String, Object> read(InputStream inputStream, boolean isExcel2003, String type) throws IOException {

        Map<String, Object> execlData = null;

        /** 根据版本选择创建Workbook的方式 */

        Workbook wb = null;

        if (isExcel2003) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        System.out.println(wb.getNumberOfSheets());
        execlData = read(wb, type);

        return execlData;

    }

    private Map<String, Object> read(Workbook wb, String type) {
        List<TableBean> tableBeans = new ArrayList<>();
        List<List<Map<TableField, String>>> dataList = new ArrayList<>();
        Map<String, Object> execlData = new HashMap<>();
        execlData.put("tableBeans", tableBeans);
        execlData.put("datas", dataList);
        if (type.equals(TABLE)) {
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                TableBean tableBean = new TableBean();
                Sheet sheet = wb.getSheetAt(i);
                int[] titleIndex = new int[4];
                for (int r = 0; r <= sheet.getLastRowNum(); r++) {
                    Row row = sheet.getRow(r);
                    if (row == null) {
                        continue;
                    }
                    Cell cell = row.getCell(0);
                    String cellValue = getCellValue(cell);
                    if (cellValue.equals("表名")) {
                        titleIndex[0] = r;
                    }
                    if (cellValue.equals("序号")) {
                        titleIndex[1] = r;
                    }
                    if (cellValue.equals("索引名称")) {
                        titleIndex[2] = r;
                    }
                    if (cellValue.equals("外键名称")) {
                        titleIndex[3] = r;
                    }
                }

                //添加其他
                for (int a = titleIndex[0] + 1; a < titleIndex[1]; a++) {
                    int cells = sheet.getRow(titleIndex[0]).getPhysicalNumberOfCells();
                    Row row = sheet.getRow(a);
                    if (row == null) {
                        continue;
                    }
                    int flagEmpty = 0;
                    for (int c = 0;c<cells;c++){
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        if (Strings.isBlank(cellValue)){
                            flagEmpty++;
                        }
                    }
                    if (flagEmpty==cells){
                        continue;
                    }
                    for (int c = 0; c < cells; c++) {
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        switch (c) {
                            case 0:
                                tableBean.setId(cellValue);
                                break;
                            case 1:
                                tableBean.setName(cellValue);
                                break;
                            case 2:
                                if (cellValue.equals("true"))
                                    tableBean.setAutoKey(true);
                                else
                                    tableBean.setAutoKey(false);
                                break;
                            default:
                                break;
                        }
                    }
                }

                //添加栏位
                for (int a = titleIndex[1] + 1; a < titleIndex[2]; a++) {
                    int cells = sheet.getRow(titleIndex[1]).getPhysicalNumberOfCells();
                    Row row = sheet.getRow(a);
                    if (row == null) {
                        continue;
                    }
                    int flagEmpty = 0;
                    for (int c = 0;c<cells;c++){
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        if (Strings.isBlank(cellValue)){
                            flagEmpty++;
                        }
                    }
                    if (flagEmpty==cells){
                        continue;
                    }
                    TableField field = new TableField();
                    for (int c = 1; c < cells; c++) {
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        switch (c) {
                            case 1:
                                field.setName(cellValue);
                                break;
                            case 2:
                                field.setType(FieldType.getType(cellValue));
                                break;
                            case 3:
                                field.setLength(new Double(cellValue).intValue());
                                break;
                            case 4:
                                field.setDecimal(new Double(cellValue).intValue());
                                break;
                            case 5:
                                if (cellValue.equals("true"))
                                    field.setNotNull(true);
                                else
                                    field.setNotNull(false);
                                break;
                            case 6:
                                field.setDescription(cellValue);
                                break;
                            case 7:
                                field.setDefaultValue(cellValue);
                                break;
                            case 8:
                                field.setAcc(cellValue);
                                break;
                            case 9:
                                if (!Strings.isBlank(cellValue)) {
                                    if (tableBean.getKey() == null)
                                        tableBean.setKey(new ArrayList<String>());
                                    tableBean.getKey().add(row.getCell(1).getStringCellValue());
                                }
                                break;
                            default:
                                break;
                        }
                    }
                    if (tableBean.getFields() == null)
                        tableBean.setFields(new ArrayList<TableField>());
                    tableBean.getFields().add(field);
                }

                //添加索引
                for (int a = titleIndex[2] + 1; a < titleIndex[3]; a++) {
                    int cells = sheet.getRow(titleIndex[2]).getPhysicalNumberOfCells();
                    Row row = sheet.getRow(a);
                    if (row == null) {
                        continue;
                    }
                    int flagEmpty = 0;
                    for (int c = 0;c<cells;c++){
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        if (Strings.isBlank(cellValue)){
                            flagEmpty++;
                        }
                    }
                    if (flagEmpty==cells){
                        continue;
                    }
                    TableIndex index = new TableIndex();
                    for (int c = 0; c < cells; c++) {
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        switch (c) {
                            case 0:
                                index.setName(cellValue);
                                break;
                            case 1:
                                if (cellValue.equals("true"))
                                    index.setUnique(true);
                                else
                                    index.setUnique(false);
                                break;
                            case 2:
                                index.setFields(cellValue);
                                break;
                            default:
                                break;
                        }
                    }
                    if (tableBean.getIndexes() == null)
                        tableBean.setIndexes(new ArrayList<TableIndex>());
                    tableBean.getIndexes().add(index);
                }

                //添加外键
                for (int a = titleIndex[3] + 1; a <= sheet.getLastRowNum(); a++) {
                    int cells = sheet.getRow(titleIndex[3]).getPhysicalNumberOfCells();
                    Row row = sheet.getRow(a);
                    if (row == null) {
                        continue;
                    }
                    int flagEmpty = 0;
                    for (int c = 0;c<cells;c++){
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        if (Strings.isBlank(cellValue)){
                            flagEmpty++;
                        }
                    }
                    if (flagEmpty==cells){
                        continue;
                    }
                    TableFk fk = new TableFk();

                    for (int c = 0; c < cells; c++) {
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        switch (c) {
                            case 0:
                                fk.setName(cellValue);
                                break;
                            case 1:
                                fk.setSourceFields(cellValue);
                                break;
                            case 2:
                                fk.setReferences(cellValue);
                                break;
                            case 3:
                                fk.setToFields(cellValue);
                                break;
                            case 4:
                                fk.setAction(cellValue);
                                break;
                            default:
                                break;
                        }
                    }
                    if (tableBean.getForeignKeys() == null)
                        tableBean.setForeignKeys(new ArrayList<TableFk>());
                    tableBean.getForeignKeys().add(fk);
                }
                tableBeans.add(tableBean);
            }
        }
        if (type.equals(DATA)) {
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                List<Map<TableField, String>> data = new ArrayList<>();
                Sheet sheet = wb.getSheetAt(i);
                Row titleRow = sheet.getRow(0);
                int cells = titleRow.getPhysicalNumberOfCells();
                int rows = sheet.getPhysicalNumberOfRows();
                String sheetName = sheet.getSheetName();
                TableBean tableBean = (TableBean) StudioApp.execute(new GetEntityCmd(sheetName));
                tableBeans.add(tableBean);
                List<TableField> tableFields = tableBean.getFields();
                for (int a = 1; a < rows; a++) {
                    Row row = sheet.getRow(a);
                    if (row == null) {
                        continue;
                    }
                    Map<TableField, String> rowData = new HashMap<>();
                    for (int c = 0; c < cells; c++) {
                        String titleVal = titleRow.getCell(c).getStringCellValue();
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        TableField tf = null;
                        for (TableField tableField : tableFields) {
                            if (tableField.getName().equals(titleVal)) {
                                tf = tableField;
                                break;
                            }
                        }
                        if(tf==null){
                            throw new RuntimeException(Labels.getLabel("editor.db.build.excel.import.fail"));
                        }
                        rowData.put(tf, cellValue);
                    }
                    data.add(rowData);
                }
                /*for (int a = 4; a < index; a++) {
                    Row row = sheet.getRow(a);
                    if (row == null) {
                        continue;
                    }
                    int cells = sheet.getRow(a).getPhysicalNumberOfCells();
                    Map<TableField, String> rowData = new HashMap<>();
                    for (int c = 0; c < cells; c++) {
                        Cell cell = row.getCell(c);
                        String cellValue = getCellValue(cell);
                        rowData.put(tableFields.get(c), cellValue);
                    }
                    data.add(rowData);
                }*/
                dataList.add(data);
            }
        }
        return execlData;
    }

    private String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    cellValue = cell.getNumericCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    cellValue = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    cellValue = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
        }
        return cellValue;
    }

}

class WDWUtil {

    public static boolean isExcel2003(String filePath) {

        return filePath.matches("^.+\\.(?i)(xls)$");

    }


    public static boolean isExcel2007(String filePath) {

        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

}
